package com.yml.mybatis.gererator.service;

import com.yml.mybatis.gererator.meta.ColumnInfo;
import com.yml.mybatis.gererator.meta.DataSourceInfo;
import com.yml.mybatis.gererator.meta.TableInfo;
import org.apache.commons.lang3.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author YML
 * @since 2023-07-21 17:07
 */
public class MySqlTable implements ITableService {

    private final DataSourceInfo db;

    public MySqlTable(DataSourceInfo db) {
        this.db = db;
    }

    @Override
    public List<TableInfo> queryAllTables() throws SQLException {
        List<TableInfo> tableInfos = new ArrayList<>();
        String builder = getTableSql();
        try (
                Connection connection = this.db.getConnection();
                PreparedStatement preparedStatement = connection.prepareStatement(builder);
                ResultSet resultSet = preparedStatement.executeQuery();
        ) {
            while (resultSet.next()) {
                String name = resultSet.getString("table_name");
                TableInfo tableInfo = new TableInfo(name, name);
                tableInfo.setComment(resultSet.getString("comment"));
                tableInfos.add(tableInfo);
            }
        }
        return tableInfos;
    }

    private String getTableSql(String... tables) {
        String builder = "select TABLE_SCHEMA as db_name,TABLE_NAME as table_name,TABLE_COMMENT as `comment` " +
                "from information_schema.TABLES " +
                "where TABLE_SCHEMA= '" + this.db.getDbName() + "' " +
                "and TABLE_TYPE = 'BASE TABLE'";
        if (tables == null || tables.length == 0) {
            return builder;
        }
        List<String> tableList = new ArrayList<>();
        for (String table : tables) {
            tableList.add("'" + table + "'");
        }
        return builder + "and TABLE_NAME in (" + String.join(",", tableList) + ")";
    }

    @Override
    public List<TableInfo> queryTables(String... tables) throws SQLException {
        List<TableInfo> tableInfos = new ArrayList<>();
        String builder = getTableSql(tables);
        try (
                Connection connection = this.db.getConnection();
                PreparedStatement preparedStatement = connection.prepareStatement(builder);
                ResultSet resultSet = preparedStatement.executeQuery();
        ) {
            while (resultSet.next()) {
                String name = resultSet.getString("table_name");
                TableInfo tableInfo = new TableInfo(name, name);
                tableInfo.setComment(resultSet.getString("comment"));
                tableInfos.add(tableInfo);
            }
        }
        return tableInfos;
    }

    @Override
    public List<ColumnInfo> queryColumns(Object table) throws SQLException {
        Connection connection = this.db.getConnection();
        List<ColumnInfo> list = new ArrayList<>();
        String format = "SELECT COLUMN_NAME AS `name`,DATA_TYPE AS type,COLUMN_COMMENT AS `comment`," +
                "(CASE WHEN IS_NULLABLE = 'NO' THEN 'Y' ELSE 'N' END) AS not_null," +
                "(CASE WHEN COLUMN_KEY = 'PRI' THEN '1' ELSE '0' END) AS is_primary_key," +
                "COLUMN_DEFAULT AS default_value FROM information_schema.COLUMNS " +
                "WHERE TABLE_SCHEMA = '%s' " +
                "AND TABLE_NAME = '%s' " +
                "order by ORDINAL_POSITION ASC";
        String sql = String.format(format, this.db.getDbName(), table);
        try (PreparedStatement preparedStatement = connection.prepareStatement(sql);
             ResultSet resultSet = preparedStatement.executeQuery();
        ) {
            while (resultSet.next()) {
                String name = resultSet.getString("name");
                String type = resultSet.getString("type");
                String comment = resultSet.getString("comment");
                String notNull = resultSet.getString("not_null");
                String defaultValue = resultSet.getString("default_value");
                if (StringUtils.isNotBlank(defaultValue)) {
                    if (StringUtils.equals(defaultValue, "(NULL)")) {
                        defaultValue = "NULL";
                    } else if (defaultValue.startsWith("((")) {
                        defaultValue = defaultValue.substring(2, defaultValue.length() - 2);
                    }
                }
                ColumnInfo column = new ColumnInfo(null, null, name, type);
                column.setComment(comment);
                column.setDefaultValue(defaultValue);
                column.setNotNull(notNull);
                column.setPrimaryKey(resultSet.getString("is_primary_key"));
                list.add(column);
            }
        }
        return list;
    }
}
